package com.neo.repository;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.neo.entity.User;

public interface UserRepository extends JpaRepository<User, Long> {

    User findById(long id);

    Long deleteById(Long id);
    
    
    List<User> findByPassword(String Password);

    @Query("select count(*)  from User where password=?1 and user_name=?2")  
    Integer countPasswordAndName(String password,String user_name);
    
    @Query("select u from User u where password=?1 and user_name=?2")  
    List<Map> selectByPasswordAndName(String password,String user_name);

    @Query("select u from User u")  
    List<User> ttt(); 
    
    @Query("select u.id, LENGTH(u.userName) as fn_len from User u where u.userName like ?1%")
    List<Object[]> findByAsArrayAndSort(String lastname);
    
    @Query(value = "select * from User ",nativeQuery = true)  //自定义原生sql
    List<User> sql2();
    
    //===============================
    
    @Query(value = "SELECT a.order_id FROM  order_base a , (SELECT MAX(created_time) AS max_created_time ,COUNT(*),user_id FROM "
	+ "order_base WHERE loan_id IS NULL AND review_status=3 GROUP BY user_id) b WHERE "
	+ "a.user_id=b.user_id "
	+ "AND a.user_id=( SELECT user_id FROM user_base WHERE phone_number =?1 ) "
	+ "AND a.created_time= b.max_created_time",nativeQuery = true)
    List<Object[]> selectOrder(String mobile);
    
//    @Query(value = "SELECT user_id FROM user_base WHERE id_code=?1 ",nativeQuery = true)
//    List<Object[]> selectUser(String sfz);
    
    
    @Modifying  
    @Transactional 
    @Query(value = "update order_base set loan_id= ?1,account_id=?2 WHERE order_id=?3 ",nativeQuery = true)
    int  updateOrder(String loan_id,String account_id,String order_id);

    
    @Query(value = "SELECT count(*) FROM order_base WHERE loan_id=?1  ",nativeQuery = true)
    int countUserIdByLoanId(String loan_id);
    
    
}